﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using WebBanThuoc.DTO;
using System.Data.SqlClient;
using WebBanThuoc.Function;

namespace WebBanThuoc.DAO
{
    public class BaiDang_DAO
    {
        private SqlConnection connection;
        //public bool hasData { set; get; }
        public BaiDang_DAO(SqlConnection connection)
        {
            this.connection = connection;
        }

        private List<BaiDang> getList(string whereCom, string orderCom, List<SqlParameter> dataPrama)
        {
            List<BaiDang> data = new List<BaiDang>();
            string comStr = "Select * from [BaiDang]";
            if (whereCom.Trim() != "")
                comStr += " where " + whereCom;
            if (orderCom.Trim() != "")
            {
                comStr += " order by " + orderCom;
            }

            try
            {
                IDataProvider dap = new DataProvider(connection);
                dap.AddParameters(dataPrama);
                dap.ExecuteReader_Command(System.Data.CommandType.Text, comStr);
                //this.hasData = dap.HasData;
                if (dap.HasData)
                {
                    while (dap.DaReader.Read())
                    {
                        BaiDang obj = new BaiDang();

                        obj.MaBaiDang = Convert.ToInt32(dap.DaReader["MaBaiDang"]);
                        obj.IDKhachHang = Convert.ToInt32(dap.DaReader["IDKhachHang"]);
                        obj.TenCauHoi = dap.DaReader["TenCauHoi"].ToString();
                        obj.NoiDung = dap.DaReader["NoiDung"].ToString();
                        obj.NgayDang = Convert.ToDateTime(dap.DaReader["NgayDang"]);
                        obj.SoLuotXem = Convert.ToInt32(dap.DaReader["SoLuotXem"]);
                        obj.SoLanTraLoi = Convert.ToInt32(dap.DaReader["SoLanTraLoi"]);
                        obj.MaDanhMuc = dap.DaReader["MaDanhMuc"].ToString();
                        obj.KiemDuyet = Convert.ToBoolean(dap.DaReader["KiemDuyet"]);
                        obj.TenNguoiHoi = dap.DaReader["TenNguoiHoi"].ToString();
                        obj.Email = dap.DaReader["Email"].ToString();
                        obj.SoDienThoai = dap.DaReader["SoDienThoai"].ToString();
                        obj.TraLoi = dap.DaReader["TraLoi"].ToString();
                        data.Add(obj);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return data;
        }

        public List<BaiDang> getListBy(string TenCauHoi, string KiemDuyet, DateTime NgayDatBegin, DateTime NgayDatEnd)
        {
            string tempS = "";
            List<SqlParameter> dataPrama = new List<SqlParameter>();
            if (KiemDuyet.Trim() != "")
            {
                tempS = " [KiemDuyet]=@KiemDuyet ";
                dataPrama.Add(
                    new SqlParameter("@KiemDuyet", System.Data.SqlDbType.Bit)
                    {
                        Value = Convert.ToBoolean(KiemDuyet)
                    });
            }
            if (NgayDatBegin != DateTime.MinValue && NgayDatEnd != DateTime.MinValue)
            {
                tempS += (tempS != "" ? " and " : "") + " [NgayDang] between @NgayDatBegin and @NgayDatEnd ";
                //SqlParameter p1 = new SqlParameter("@NgayDatBegin", System.Data.SqlDbType.SmallDateTime);
                //p1.Value = NgayDatBegin;
                //SqlParameter p2 = new SqlParameter("@NgayDatBegin", System.Data.SqlDbType.SmallDateTime) { };
                //p1.Value = NgayDatBegin;
                dataPrama.Add(
                    new SqlParameter("@NgayDatBegin", System.Data.SqlDbType.SmallDateTime)
                    {
                        Value = Func.getMinTimeInDateTime(NgayDatBegin)
                    });
                dataPrama.Add(
                    new SqlParameter("@NgayDatEnd", System.Data.SqlDbType.SmallDateTime)
                    {
                        Value = Func.getMaxTimeInDateTime(NgayDatEnd)
                    });
            }

            tempS += (tempS != "" ? " and " : "") + " [TenCauHoi] like N'%" + TenCauHoi + "%' ";

            return getList(tempS, " [NgayDang] asc", dataPrama);
        }

        private int update(BaiDang obj, string whereCom)
        {
            string comQuery = "UPDATE [BaiDang] SET [MaBaiDang]=@MaBaiDang, [TenCauHoi]=@TenCauHoi, [NoiDung]=@NoiDung"
                + ", [NgayDang]=@NgayDang, [SoLuotXem]=@SoLuotXem, [SoLanTraLoi]=@SoLanTraLoi, [MaDanhMuc]=@MaDanhMuc"
                + ", [IDKhachHang]=@IDKhachHang, [KiemDuyet]=@KiemDuyet, [TenNguoiHoi]=@TenNguoiHoi, [Email]=@Email, [SoDienThoai]=@SoDienThoai, [TraLoi]=@TraLoi ";
            
            if (whereCom.Trim() != "")
                comQuery += " where " + whereCom;
            try
            {
                IDataProvider dap = new DataProvider(connection);
                dap.AddParameters("@MaBaiDang", obj.MaBaiDang, System.Data.SqlDbType.Int);
                dap.AddParameters("@IDKhachHang", obj.IDKhachHang, System.Data.SqlDbType.Int);
                dap.AddParameters("@TenCauHoi", obj.TenCauHoi, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@NoiDung", obj.NoiDung, System.Data.SqlDbType.NText);
                dap.AddParameters("@NgayDang", obj.NgayDang, System.Data.SqlDbType.SmallDateTime);
                dap.AddParameters("@SoLuotXem", obj.SoLuotXem, System.Data.SqlDbType.Int);
                dap.AddParameters("@SoLanTraLoi", obj.SoLanTraLoi, System.Data.SqlDbType.Int);
                dap.AddParameters("@MaDanhMuc", obj.MaDanhMuc, System.Data.SqlDbType.NChar);
                dap.AddParameters("@KiemDuyet", obj.KiemDuyet, System.Data.SqlDbType.Bit);
                dap.AddParameters("@TenNguoiHoi", obj.TenNguoiHoi, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@Email", obj.Email, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@SoDienThoai", obj.SoDienThoai, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@TraLoi", obj.TraLoi, System.Data.SqlDbType.NText);

                return dap.ExecuteNonQuery_Command(System.Data.CommandType.Text, comQuery);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int updateByKey(BaiDang data, int MaBaiDang)
        {
            return update(data, " [MaBaiDang]=" + MaBaiDang);
        }

        public int createMaBaiDang()
        {
            int result = 1;
            try
            {
                IDataProvider dap = new DataProvider(connection);
                dap.ExecuteReader_Command(System.Data.CommandType.Text, "SELECT [dbo].[fnc_CreateNew_MaBaiDang] () as col");
                //this.hasData = dap.HasData;
                if (dap.HasData)
                {
                    dap.DaReader.Read();
                    result = Convert.ToInt32(dap.DaReader["col"]);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        private BaiDang getBy(string whereCom, string orderCom)
        {
            BaiDang data = null;
            string comStr = "Select * from [BaiDang]";
            if (whereCom.Trim() != "")
                comStr += " where " + whereCom;
            if (orderCom.Trim() != "")
            {
                comStr += " order by " + orderCom;
            }

            try
            {
                IDataProvider dap = new DataProvider(connection);                
                dap.ExecuteReader_Command(System.Data.CommandType.Text, comStr);
                //this.hasData = dap.HasData;
                if (dap.HasData)
                {
                    dap.DaReader.Read();
                    data = new BaiDang();
                    data.MaBaiDang = Convert.ToInt32(dap.DaReader["MaBaiDang"]);
                    data.IDKhachHang = Convert.ToInt32(dap.DaReader["IDKhachHang"]);
                    data.TenCauHoi = dap.DaReader["TenCauHoi"].ToString();
                    data.NoiDung = dap.DaReader["NoiDung"].ToString();
                    data.NgayDang = Convert.ToDateTime(dap.DaReader["NgayDang"]);
                    data.SoLuotXem = Convert.ToInt32(dap.DaReader["SoLuotXem"]);
                    data.SoLanTraLoi = Convert.ToInt32(dap.DaReader["SoLanTraLoi"]);
                    data.MaDanhMuc = dap.DaReader["MaDanhMuc"].ToString();
                    data.KiemDuyet = Convert.ToBoolean(dap.DaReader["KiemDuyet"]);
                    data.TenNguoiHoi = dap.DaReader["TenNguoiHoi"].ToString();
                    data.Email = dap.DaReader["Email"].ToString();
                    data.SoDienThoai = dap.DaReader["SoDienThoai"].ToString();
                    data.TraLoi = dap.DaReader["TraLoi"].ToString();

                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return data;
        }

        public BaiDang getByKey(int MaBaiDang)
        {
            return getBy(" [MaBaiDang]=" + MaBaiDang, "");
        }

        private int delete(string whereCom)
        {
            string comUpdate = "DELETE FROM [BaiDang] ";
            if (whereCom.Trim() != "")
                comUpdate += " where " + whereCom;
            try
            {
                IDataProvider dap = new DataProvider(this.connection);
                return dap.ExecuteNonQuery_Command(System.Data.CommandType.Text, comUpdate);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int deleteByKey(int MaBaiDang)
        {
            return delete(" [MaBaiDang]=" + MaBaiDang);
        }

        public int add(BaiDang obj)  //thêm bài đăng
        {

            string comQuery = "INSERT INTO [BaiDang] ([MaBaiDang],[TenNguoiHoi]"
                + ",[Email],[SoDienThoai],[NoiDung],[NgayDang],[SoLuotXem],[SoLanTraLoi],[MaDanhMuc],[KiemDuyet],[TenCauHoi], [IDKhachHang], [TraLoi])"
                + " VALUES (@MaBaiDang,@TenNguoiHoi,@Email,@SoDienThoai"
                + ",@NoiDung,@NgayDang,@SoLuotXem,@SoLanTraLoi,@MaDanhMuc,@KiemDuyet,@TenCauHoi,@IDKhachHang,@TraLoi)";

            try
            {
                IDataProvider dap = new DataProvider(connection);

                dap.AddParameters("@MaBaiDang", obj.MaBaiDang, System.Data.SqlDbType.Int);
                dap.AddParameters("@TenNguoiHoi", obj.TenNguoiHoi, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@Email", obj.Email, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@SoDienThoai", obj.SoDienThoai, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@NoiDung", obj.NoiDung, System.Data.SqlDbType.NText);
                dap.AddParameters("@NgayDang", obj.NgayDang, System.Data.SqlDbType.SmallDateTime);
                dap.AddParameters("@SoLuotXem", obj.SoLuotXem, System.Data.SqlDbType.Int);
                dap.AddParameters("@SoLanTraLoi", obj.SoLanTraLoi, System.Data.SqlDbType.Int);
                dap.AddParameters("@MaDanhMuc", obj.MaDanhMuc, System.Data.SqlDbType.NChar);
                dap.AddParameters("@KiemDuyet", obj.KiemDuyet, System.Data.SqlDbType.Bit);
                dap.AddParameters("@TenCauHoi", obj.TenCauHoi, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@TraLoi", obj.TraLoi, System.Data.SqlDbType.NText);
                dap.AddParameters("@IDKhachHang", obj.IDKhachHang, System.Data.SqlDbType.Int);
                return dap.ExecuteNonQuery_Command(System.Data.CommandType.Text, comQuery);
            }
            catch (Exception ex)
            {
                throw ex;
            }


        }


        private List<BaiDang> getList_Paging_By(string whereCom, int pageIndex, int pageSize)
        {
            int begin = (pageIndex - 1) * pageSize + 1;
            int end = pageIndex * pageSize;
            List<BaiDang> data = new List<BaiDang>();

            if (whereCom.Trim() != "")
            {
                whereCom = " where " + whereCom;
            }

            string comStr = "Select * from (select *, ROW_NUMBER() Over(order by [NgayDang] desc) as rowNumber from [BaiDang] " + whereCom + " ) sp ";
            comStr += " where [rowNumber] between " + begin.ToString() + " and " + end.ToString();
            //comStr += " and [KiemDuyet]=1  order by [NgayDang] desc ";
            try
            {
                IDataProvider dap = new DataProvider(connection);
                dap.ExecuteReader_Command(System.Data.CommandType.Text, comStr);
                //this.hasData = dap.HasData;
                if (dap.HasData)
                {
                    while (dap.DaReader.Read())
                    {
                        BaiDang obj = new BaiDang();

                        obj.MaBaiDang = Convert.ToInt32(dap.DaReader["MaBaiDang"]);
                        obj.TenNguoiHoi = dap.DaReader["TenNguoiHoi"].ToString();
                        obj.Email = dap.DaReader["Email"].ToString();
                        obj.SoDienThoai = dap.DaReader["SoDienThoai"].ToString();
                        obj.NoiDung = dap.DaReader["NoiDung"].ToString();
                        obj.NgayDang = Convert.ToDateTime(dap.DaReader["NgayDang"]);
                        obj.SoLuotXem = Convert.ToInt32(dap.DaReader["SoLuotXem"]);
                        obj.SoLanTraLoi = Convert.ToInt32(dap.DaReader["SoLanTraLoi"]);
                        obj.MaDanhMuc = dap.DaReader["MaDanhMuc"].ToString();
                        obj.KiemDuyet = Convert.ToBoolean(dap.DaReader["KiemDuyet"]);
                        obj.TenCauHoi = dap.DaReader["TenCauHoi"].ToString();
                        obj.TraLoi = dap.DaReader["TraLoi"].ToString();
                        obj.IDKhachHang = Convert.ToInt32(dap.DaReader["IDKhachHang"]);

                        obj.TenCauHoi = dap.DaReader["TenCauHoi"].ToString();
                        obj.TraLoi = dap.DaReader["TraLoi"].ToString();
                        //obj.IdKhachHang = Convert.ToInt32(dap.DaReader["IdKhachHang"]);

                        data.Add(obj);

                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return data;
        }
        public List<BaiDang> getList_Paging_All(int pageIndex, int pageSize)
        {
            return getList_Paging_By(" [KiemDuyet]=1 ", pageIndex, pageSize);
        }
        public int totalNumberOfPages_All(int pageSize)
        {
            return totalNumberOfPagesBy(" [KiemDuyet]=1 ", pageSize);
        }
        private int totalNumberOfPagesBy(string whereCom, int pageSize)
        {
            int result = 0;
            try
            {
                string comStr = "select count([MaBaiDang]) as col from [BaiDang] ";
                if (whereCom.Trim() != "")
                {
                    comStr += " where " + whereCom;
                }


                IDataProvider dap = new DataProvider(connection);
                dap.ExecuteReader_Command(System.Data.CommandType.Text, comStr);
                //this.hasData = dap.HasData;
                if (dap.HasData)
                {
                    dap.DaReader.Read();
                    result = Convert.ToInt32(dap.DaReader["col"]);
                    if (result % pageSize != 0)
                    {
                        result = result / pageSize + 1;
                    }
                    else
                    {
                        result = result / pageSize;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
    }
}